Overview

This control can select a record from a database for input validation and for subsequent data selection and update operations by DataSwitch, DataChange, and Dial controls. The DataFind control accesses databases using one of three methods:

1. ODBC drivers

2. Microsoft Jet Engine in conjunction with a Visual Basic Data control.

NOTE: This method is not supported in Visual Studio .NET.

3. Microsoft Jet Engine using recordsets created in VB code.

NOTE: This method is not supported in Visual Studio .NET.

DataFind can search using data from other controls, including collected digits, and can search using several different techniques. The search can start from the current position in the database or from the beginning. See Using Databases. Recordsets can be created for each call based on input from the caller.

Once a record is found, fields in the record are available for use by other controls and from code.

If the control is entered through the top input, the DataFind control performs a MoveFirst and then searches for the first database record matching the search criteria.

If the control is entered through the Next input, it performs a MoveNext on the recordset and if matching is enabled, it searches for a record that matches the specifications supplied in the Matching property page. If this is the first search since the recordset was assigned or if it has been reset with the Reset() method, the database is searched from the beginning (the control does not perform a MoveNext).

The position in the recordset can be set back to the beginning using the Reset() method. See the DialDB example form. The DataFind control does not support traversing the database in a backwards direction.

Generally each channel either has its own recordset or maintains its own position in a shared recordset, dependent on the database type. This allows each channel to run independently. A flag can be set so that each channel shares the same recordset and position.

Exit to...

If the control is entered through the main input and the record is not found, the call is routed to the None (bottom) node.

If the control is entered through the Next input and the record is not found, the call is routed to the End (or second) node.

If the record is found, the call is routed to the Found (top) node.

Searching for Data

There are two basic ways of selecting the records of interest in a database:

1. Find data matching

   VBVoice provides some basic data matching capability whereby it searches for a record using a simple matching criteria against one field in the database. This matching criteria compares a field in the database against some provided data, which can include caller's input or other database data, and selects each matching record. Thus, callers can access different sets of records based on their selections.

2. Using custom recordsets

   Another way of creating a recordset based on the callers' requirements is to use the ODBC capability or to create recordsets in code. This allows you to create more complex queries based on more than one field using SQL statements. See Filters (ODBC).

Data Matching Setup

If Find all records in a recordset option is selected, the DataFind control iterates through all records one at a time, moving forward one record each time the control is entered by a call.

If Records where Field Matches option is selected, the DataFind control searches the database looking in the specified field for the data, using the Match specifications.

Matching data has two criteria: using a digit to alphanumeric translation; i.e. translating the letters on the telephone keypad to the corresponding numbers, which can be turned on by selecting the Match using digit translation option, and off by selecting the Match characters exactly option. And whether to do a full or partial field matching by selecting one of the two options Match first characters or All characters must match.

EXAMPLE

The supplied digits 12 matches database records containing 123, 1245, and 12, but not 1 or 13. Using digit translation, the data field 234 matches ADH and BEG.

If the database is empty, the call exits out of the None output. This is the case even if the DataFind is only being used to load a database for update using a DataChange or Dial control. Assuring that there is always at least one record in your database avoids ambiguity when interpreting the None result of the find operation.

Note that it is preferable to use a filter to select the data you want and then to select All Records in the Data Matching property page, rather than to use Data matching to select the records. This causes the database to do the work of finding the required records, rather than the control, thus reducing the performance overhead. See Filters (ODBC).

Sharing a Recordset between Channels

The DataFind control generally keeps track of the position in the database for each channel. Sometimes it is desirable to maintain a position independent of channel, for instance, in an out-dialling application where each number in a database should be dialled once regardless of which channel performs the operation. In this scenario the recordset should be set to Shared, which makes the position global (i.e. each channel that enters the control uses and moves a common position pointer).

Sharing the Recordset:

To share using a recordset created by code:

1. Use the SetRecSource method with the channel parameter set to -1.

2. All channels then share the same recordset.

To share using ODBC databases:

1. Check the Share Recordset checkbox in the property page.

   NOTE: This requires an ODBC driver that supports bookmarks.

2. The Filter statement should not contain any references to control properties (because these are channel specific).

3. If you are accessing a field in the shared Recordset (either within DataFind or from another control), you can ensure that each channel is getting a different record if you set the GlobalDataSync key to 1 in VBVOICE.INI DataFind section. Running without synchronization (by setting GlobalDataSync to 0), gives better performance, but may cause a race condition of multiple channels updating the global data.

To share using a Data Control:

1. Check the Share Recordset checkbox in the property page.

2. Although the Data Control is always shared between channels, the DataFind control generally keeps a bookmark for the position of each channel. If Share Recordset is checked, it uses the same bookmark for each channel.

Performing Search by Name

To use the DataFind control for directory lookup, use Find record using... Alpha digits to perform numeric to alphabetic translation and use Match On: First so it finds all the records that start with the digits that the caller enters. See the Directory page on the Voicemail example.

Accessing Data in a Database

Once a record has been found in the database, DataFind retrieves the data values for the fields specified in the Database property page. These data values can then be used by subsequent controls as necessary; for instance, in the example below the database is providing a DialNumber field. To use this data in a Dial control, set the number to dial in the Dial control to %DataFind1.DialNumber%. Additional fields could be used elsewhere as required. The data can also be accessed from code using the Fields object:

Print DataFind1.Fields.MyField(Channel)

Note: The record must contain data in each field to be accessed. Fields that contain null data will cause an error.

Choosing the Database Type

The easiest way to access a database is to use the VB Data control. When using the Jet engine, either through a recordset created in code or by using the Data control, all accesses to the database are serialized (see Multi-threading below).

Better performance can be achieved by:

1. Running each channel in its own copy of the application

2. Running the data access for each channel in its own OLE server, or

3. Using the VBVoice ODBC support with multi-threaded ODBC drivers.

The Data control can be used to access both local databases and networked databases. Networked databases can be accessed either directly over the network or by using Access to set up an attached database. ODBC can also be used for both local and remote databases.

Using ODBC provides a capability of creating a different recordset for each channel using criteria based on callers' input. This is more efficient and more flexible than performing data matching. Creating recordsets in code also provides this capability, but at the expense of more coding work. ODBC can be more efficient when accessing databases over a network.

Multi-Threading

Multi-threading is important when accessing large databases in multi-channel applications. Microsoft Jet database drivers are not multi-threaded so each channel must serialize its database access through one thread. One channel doing a large database access may delay other channels. In this situation, ODBC provides better performance.

Using a VB Data Control

At first sight the Visual Basic Data control appears to have a daunting array of properties, however it requires only two properties to be set in order to connect it to a database:

The DataSource property in the DataFind control must be set to the name of the data control. This setting is not accessible from the property page, but must be made using the Visual Basic properties window.

If you are not writing to your database, you can improve performance by setting the ReadOnly property to True.

Filters (Data Control)

When using a VB Data control directly, any filter applied using the RecordSource property of the Data control is applied to all channels. Because the recordset in the Data control is shared by all channels, the RecordSource property should not be changed once the system has started. If each call requires a recordset to be created dynamically based on caller input, either an ODBC database or a recordset created by code should be used.

A sample SQL statement for the RecordSource property:

SELECT Titles.Title, Author FROM Titles, Authors WHERE [Titles.AU_ID] = '1234'

Using a Recordset Created in VB Code

Creating Recordsets

Recordsets can be created in code or the recordset in a Data control can be assigned to the DataFind control using code. When using code, each channel can be assigned a different recordset. If you wish to share the same recordset between channels, use SetRecSource with a channel parameter of -1.

Assigning the Recordset Created in Code

Form declarations:

Dim MyDB As Database, MyData As Recordset

In Form.Load:

Set MyDB = Workspaces(0).OpenDatabase("c:\ inventry.mdb")

In the Enter event of the DataFind control:

Set MyData = MyDB.OpenRecordset("Inventory", dbOpenDynaset)

DataFind1.SetRecSource channel, MyData

Assigning a Recordset Derived from a Data Control

In the Enter event of the DataFind control:

DataFind1.SetRecSource channel, Data1.Recordset

The recordset can be manipulated using the underlying recordset object or data control. For instance, if the MoveFirst method is executed on the recordset, the next access by the DataFind starts searching from the first record.

Filters (Jet)

You can generate custom queries for each channel when using recordsets created in code by adding a WHERE clause to the RecordSource property of the Data control or Dynaset.

Instead of using a table name, use:

Find [tablename] WHERE field = value to get a subset of records.

Using an ODBC Database

To open a database using ODBC, you must first install the appropriate driver. Visual Basic comes with ODBC drivers for most common databases. The DataFind control can access fields that use number or character string formats. Note that ODBC drivers are not installed by Visual Basic Standard Edition.

Creating an ODBC Data Source from an Existing Database

To create an ODBC data source from an existing database:

1. Open the Database property page and click the browse button ("...")

2. In the SQL Data Sources dialog, click New

3. Select the appropriate driver (Microsoft Access for MDB databases)

4. Click OK

5. In the Microsoft Access database setup dialog, click Select

6. Choose the database file you want to use and click OK

7. Type in a name for the database in the Data Source name field and click OK

8. Select the desired table name and fields

Filters (ODBC)

You can use the Filter string to add any SQL statement to the default query generated by the DataFind control. The control generates a query of the form SELECT field1, field 2, and so on, FROM table using the field names and table name provided in the Database property page.

The Filter string is concatenated onto the end of the query, allowing you to add WHERE and ORDERBY or other clauses to further qualify the data you want in the recordset. Each call will have its own recordset (unless Share Recordset is checked), so the Filter statement can contain references to other controls, allowing each call to have a different recordset. Note, however, that each recordset must access the same set of fields.

If the Filter string starts with a > character, it will completely override the SQL string generated by VBVoice. See the second example below.

EXAMPLES

WHERE [Attempts] = '0'

>SELECT [Attempts] FROM [Counts] WHERE [Attempts] = '0'

In addition, each channel can use its own variant of the statement by using a control property:

SELECT Titles.Title.Dept, Author FROM Titles, Authors WHERE [Titles.AU_ID] = '%GetDigits1.Digits%

DataFind Examples

Use of the DataFind control is shown in these examples:

DialDB
DialODBC
Inventory